﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Xml;

using MicroDb.Mysql;

using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;

using MySqlConnector;

using Utils;

namespace MicroDb.Helper
{
    public class CodeFirst
    {
        private readonly IDictionary<string, (MicroDbType, string)> dbConnection;

        private readonly ILogger<CodeFirst> logger;
        private readonly IServiceProvider serviceProvider;
        public CodeFirst(IOptions<MicroDbOptions> dbOptions, IServiceProvider serviceProvider, ILogger<CodeFirst> logger)
        {
            this.dbConnection = dbOptions.Value.DbConnectionBuilders;
            this.serviceProvider = serviceProvider;
            this.logger = logger;
        }



        public void Use(Type[] types)
        {
            var xml = GetXml(types);
            if (xml == null) return;
            Task.Factory.StartNew(() =>
            {
                foreach (var item in dbConnection)
                {
                    var dbTypes = types.Where(a => item.Key == a.GetCustomAttribute<DbSourceAttribute>()?.Name).ToArray();
                    if (dbTypes.Length == 0) continue;

                    switch (item.Value.Item1)
                    {
                        case MicroDbType.MySql:
                            if (CheckDbBaseMysql(item.Key, item.Value.Item2))
                            {
                                ContrastMysqlTables(xml, dbTypes, item.Key, item.Value.Item2);
                            }
                            else
                            {
                                CreateMysqlCreateTables(xml, dbTypes, item.Key, item.Value.Item2);
                            }
                            break;
                        case MicroDbType.SqlServer:
                            break;
                        case MicroDbType.Oracle:
                            break;
                        default:
                            break;
                    }
                }
            });
        }

        private Dictionary<string, string> GetXml(Type[] types)
        {
            if (types.Length == 0) return null;
            string name = types.FirstOrDefault().Assembly.GetName().Name + ".xml";
            var file = Path.Combine(AppContext.BaseDirectory, name);
            if (!File.Exists(file))
            {
                logger.LogInformation($"CodeFirst 需提供{name}");
                return null;
            }
            XmlDocument xmlDocument = new XmlDocument();
            xmlDocument.Load(file);
            var dict = new Dictionary<string, string>();
            foreach (XmlNode item in xmlDocument.SelectSingleNode("doc/members").ChildNodes)
            {
                if (item.OuterXml.Contains("<remarks>"))
                {
                    dict.Add(item.Attributes["name"].Value, item["remarks"].InnerText);
                }

            }
            return dict;
        }


        private bool CheckDbBaseMysql(string dbSource, string connectionString)
        {
            var dbConnectionStringBuilder = new MySqlConnectionStringBuilder(connectionString);
            string dbBase = dbConnectionStringBuilder.Database;
            dbConnectionStringBuilder.Database = "mysql";

            var sqlexce = DBQueryProviderFactory.GetSqlExecute(serviceProvider, "_mysql", dbConnectionStringBuilder.ConnectionString, MicroDbType.MySql);

            var list = sqlexce.ExecuteList<string>(CommandType.Text, "show databases;", null);
            if (list.Contains(dbBase))
            {
                return true;
            }
            //创建数据库
            sqlexce.ExecuteNonQuery($"CREATE DATABASE `{dbBase}` CHARACTER SET utf8 COLLATE utf8_general_ci;");

            return false;
        }
        private void CreateMysqlCreateTables(Dictionary<string, string> xmlNodeList, Type[] dbTypes, string dbSource, string connectionString)
        {
            var sqlexce = DBQueryProviderFactory.GetSqlExecute(serviceProvider, dbSource, connectionString, MicroDbType.MySql);
            foreach (var type in dbTypes)
            {
                string name = ChangeName(type.Name);
                CreateMysqlCreateTables(xmlNodeList, name, type, sqlexce);
            }
        }
        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="type"></param>
        /// <param name="sqlexce"></param>
        private void CreateMysqlCreateTables(Dictionary<string, string> xmlNodeList, string name, Type type, SqlExecute sqlexce)
        {
            string tableEnd = xmlNodeList["T:" + type.FullName];
            var cols = type.GetProperties().Select(a =>
            {
                var col = a.GetCustomAttribute<ColumnAttribute>(true);
                if (col == null) return null;
                col.Name = a.Name;
                return col;
            }).Where(a => a != null).ToArray();

            string ps = string.Join(",", cols.Select(a => $"`{ChangeName(a.Name)}` {xmlNodeList["P:" + type.FullName + "." + a.Name]}"));

            var primary = cols.FirstOrDefault(a => a is PrimaryAttribute);
            if (primary != null)
            {
                ps += $",PRIMARY KEY (`{ChangeName(primary.Name)}`)";
            }
            string sql = $"CREATE TABLE `{name}` ({ps}) {tableEnd};";
            sqlexce.ExecuteNonQuery(sql);
        }

        private void TryCreateColumns(Dictionary<string, string> xmlNodeList, string name, Type type, SqlExecute sqlexce)
        {

            var list = sqlexce.ExecuteList<COLUMN>(CommandType.Text, $"SHOW COLUMNS FROM `{name}`;", null);

            var cols = type.GetProperties().Select(a =>
            {
                var col = a.GetCustomAttribute<ColumnAttribute>(true);
                if (col == null) return null;
                col.Name = a.Name;
                return col;
            }).Where(a => a != null).ToArray();
            StringBuilder sb = new StringBuilder();
            foreach (var col in cols)
            {
                string colName = ChangeName(col.Name);
                var remark = xmlNodeList["P:" + type.FullName + "." + col.Name];
                var dbcol = list.FirstOrDefault(a => a.Field == colName);
                if (dbcol == null)
                {// 数据表中不存在字段，新增
                    sb.AppendLine($"ALTER TABLE `{name}` ADD `{colName}` {remark};");
                }
                else
                {
                    if (dbcol.Type != remark.Split(' ')[0])
                    {
                        logger.LogInformation($"字段 `{name}.{colName}` 属性发生变化，请手动修改");
                    }
                }
            }
            if (sb.Length > 0)
                sqlexce.ExecuteNonQuery(sb.ToString());
        }
        private void ContrastMysqlTables(Dictionary<string, string> xmlNodeList, Type[] dbTypes, string dbSource, string connectionString)
        {
            var sqlexce = DBQueryProviderFactory.GetSqlExecute(serviceProvider, dbSource, connectionString, MicroDbType.MySql);
            var list = sqlexce.ExecuteList<string>(CommandType.Text, "show tables;", null);
            foreach (var item in dbTypes)
            {
                string name = ChangeName(item.Name);
                if (!list.Contains(name))
                {
                    CreateMysqlCreateTables(xmlNodeList, name, item, sqlexce);
                }
                else
                {
                    TryCreateColumns(xmlNodeList, name, item, sqlexce);
                }
            }
        }
        private static string ChangeName(string name)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var ch in name)
            {
                if (ValidatorHelper.Between(ch, 'A', 'Z'))
                {
                    if (sb.Length > 0)
                        sb.Append('_');
                    sb.Append((char)(ch + 0x20));
                }
                else
                {
                    sb.Append(ch);
                }
            }
            return sb.ToString();
        }
        class COLUMN
        {
            public string Field { get; set; }
            public string Type { get; set; }
        }

    }
}
